import pandas as pd
import os
import glob
from openpyxl import Workbook
from datetime import datetime
import flet as ft
def main(page: ft.Page):
    page.title = "Cpk Summary Export"
    page.window.width = 300
    page.window.height = 135
    info_text=ft.Text("Ready",size=20,text_align=ft.TextAlign.CENTER)
    info=ft.Card(content=info_text,width=page.window.width,color=ft.colors.AMBER_200,)
    def export(e):
        info_text.value="Exporting..."
        page.update()
        folder_path = 'd:/data'
        output_file = 'D:/Cpk Summary.xlsx'
        final = []
        excel_files =glob.glob(os.path.join(folder_path, '*.xlsx'))
        for excel_file in excel_files:
            with pd.ExcelFile(excel_file) as xlsx:
                sheet=xlsx.sheet_names
                for sht in sheet:
                    if sht[:10]=="Data Input":
                        df=pd.read_excel(excel_file,sht)
                        name=df.iloc[4].tolist()
                        description=df.iloc[5].tolist()
                        for i in range(len(name)):
                            final.append([name[i],description[i]])
                        break
                for sht in sheet:
                    if sht[:10]=="Data Input":
                        df=pd.read_excel(excel_file,sht)
                        nor=df.iloc[12].tolist()
                        tola=df.iloc[13].tolist()
                        tolb=df.iloc[14].tolist()
                        stdv=df.iloc[19].tolist()
                        mean=df.iloc[20].tolist()
                        cp=df.iloc[23].tolist()
                        cpk=df.iloc[26].tolist()
                        yie=df.iloc[28].tolist()
                        for i in range(len(nor)):
                            final[i].append(nor[i])
                            final[i].append(tola[i])
                            final[i].append(tolb[i])
                            final[i].append(stdv[i])
                            final[i].append(mean[i])
                            final[i].append(cp[i])
                            final[i].append(cpk[i])
                            final[i].append(yie[i])
                            final[i].append("")
        wb=Workbook()
        ws=wb.active
        ws.title="data"
        for s in range(14,len(final)):
            ws.append(final[s])
        wb.save(output_file)
        info_text.value="Done"
        page.update()
    page.add(
        ft.ElevatedButton(text="Export",on_click=export,icon=ft.icons.EXPLORE,width=page.window.width),
        info
    )
ft.app(target=main)